In [3]:
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import seaborn as sns
import csv
In [4]:
df = pd.read_csv(r'C:\Users\sarahd\Desktop\light_duty_vehicles.csv',low_memory=False)
In [5]:
df
Out[5]:
Unnamed: 0 Vehicle_ID Model Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined ... Engine_Description Manufacturer Category Fuel Electric-Only_Range PHEV_Total_Range PHEV_Type Drivetrain combined_fuel_economy Name
0 0 13044.0 NSX 2022.0 NaN NaN NaN 21.0 22.0 21.0 ... 3.5L V6 Acura Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Acura NSX
1 1 12854.0 A3 2022.0 NaN NaN NaN 29.0 38.0 32.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN FWD NaN Audi A3
2 2 12842.0 A3 quattro 2022.0 NaN NaN NaN 28.0 36.0 31.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A3 quattro
3 3 12783.0 A4 allroad quattro 2022.0 NaN NaN NaN 24.0 30.0 26.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 allroad quattro
4 4 12782.0 A4 quattro 2022.0 NaN NaN NaN 26.0 34.0 29.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 quattro
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
1048570 1048570 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048571 1048571 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048572 1048572 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048573 1048573 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048574 1048574 NaN NaN NaN NaN NaN NaN NaN 66.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

1048575 rows × 24 columns

In [11]:
df.head()
Out[11]:
Unnamed: 0 Vehicle_ID Model Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined ... Engine_Description Manufacturer Category Fuel Electric-Only_Range PHEV_Total_Range PHEV_Type Drivetrain combined_fuel_economy Name
0 0 13044.0 NSX 2022.0 NaN NaN NaN 21.0 22.0 21.0 ... 3.5L V6 Acura Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Acura NSX
1 1 12854.0 A3 2022.0 NaN NaN NaN 29.0 38.0 32.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN FWD NaN Audi A3
2 2 12842.0 A3 quattro 2022.0 NaN NaN NaN 28.0 36.0 31.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A3 quattro
3 3 12783.0 A4 allroad quattro 2022.0 NaN NaN NaN 24.0 30.0 26.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 allroad quattro
4 4 12782.0 A4 quattro 2022.0 NaN NaN NaN 26.0 34.0 29.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 quattro

5 rows × 24 columns

In [12]:
df.describe()
Out[12]:
Unnamed: 0 Vehicle_ID Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined Engine_Cylinder_Count Electric-Only_Range PHEV_Total_Range combined_fuel_economy
count 1.048575e+06 3008.00000 3008.000000 1595.000000 1505.000000 467.000000 2044.000000 2047.000000 763.000000 2443.000000 374.000000 43.000000 253.000000
mean 5.242870e+05 9424.59242 2014.493019 38.439298 37.392651 72.126338 22.781605 27.360430 27.832241 6.085960 148.251337 450.465116 75.418972
std 3.026977e+05 4667.98027 6.722191 47.778798 42.724296 36.776309 10.794132 9.105821 10.265666 1.762977 128.708353 93.042578 41.141335
min 0.000000e+00 1.00000 1991.000000 0.000000 0.000000 10.000000 0.000000 0.000000 15.000000 0.000000 8.000000 290.000000 25.000000
25% 2.621435e+05 10280.75000 2012.000000 11.000000 16.000000 49.000000 16.000000 21.000000 21.000000 4.000000 21.000000 380.000000 34.000000
50% 5.242870e+05 11560.50000 2016.000000 14.000000 20.000000 74.000000 19.000000 26.000000 24.000000 6.000000 126.000000 460.000000 74.000000
75% 7.864305e+05 12336.25000 2020.000000 60.000000 41.000000 103.500000 26.000000 31.000000 31.000000 8.000000 254.750000 520.000000 99.000000
max 1.048574e+06 13105.00000 2022.000000 800.000000 800.000000 142.000000 66.000000 66.000000 59.000000 12.000000 520.000000 640.000000 187.000000
In [13]:
df.nunique()
Out[13]:
Unnamed: 0                            1048575
Vehicle_ID                               3008
Model                                    1286
Model_Year                                 32
Alternative_Fuel_Economy_City             138
Alternative_Fuel_Economy_Highway          116
Alternative_Fuel_Economy_Combined          96
Conventional_Fuel_Economy_City             54
Conventional_Fuel_Economy_Highway          51
Conventional_Fuel_Economy_Combined         41
Transmission_Type                          64
Engine_Type                               115
Engine_Size                               255
Engine_Cylinder_Count                       8
Engine_Description                        356
Manufacturer                               55
Category                                    6
Fuel                                       11
Electric-Only_Range                       144
PHEV_Total_Range                           25
PHEV_Type                                   2
Drivetrain                                  5
combined_fuel_economy                      72
Name                                     1292
dtype: int64
In [14]:
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1048575 entries, 0 to 1048574
Data columns (total 24 columns):
 #   Column                              Non-Null Count    Dtype  
---  ------                              --------------    -----  
 0   Unnamed: 0                          1048575 non-null  int64  
 1   Vehicle_ID                          3008 non-null     float64
 2   Model                               3008 non-null     object 
 3   Model_Year                          3008 non-null     float64
 4   Alternative_Fuel_Economy_City       1595 non-null     float64
 5   Alternative_Fuel_Economy_Highway    1505 non-null     float64
 6   Alternative_Fuel_Economy_Combined   467 non-null      float64
 7   Conventional_Fuel_Economy_City      2044 non-null     float64
 8   Conventional_Fuel_Economy_Highway   2047 non-null     float64
 9   Conventional_Fuel_Economy_Combined  763 non-null      float64
 10  Transmission_Type                   2924 non-null     object 
 11  Engine_Type                         2211 non-null     object 
 12  Engine_Size                         2874 non-null     object 
 13  Engine_Cylinder_Count               2443 non-null     float64
 14  Engine_Description                  1971 non-null     object 
 15  Manufacturer                        3008 non-null     object 
 16  Category                            3008 non-null     object 
 17  Fuel                                3008 non-null     object 
 18  Electric-Only_Range                 374 non-null      float64
 19  PHEV_Total_Range                    43 non-null       float64
 20  PHEV_Type                           176 non-null      object 
 21  Drivetrain                          329 non-null      object 
 22  combined_fuel_economy               253 non-null      float64
 23  Name                                3008 non-null     object 
dtypes: float64(12), int64(1), object(11)
memory usage: 192.0+ MB
In [15]:
df.columns.tolist()
Out[15]:
['Unnamed: 0',
 'Vehicle_ID',
 'Model',
 'Model_Year',
 'Alternative_Fuel_Economy_City',
 'Alternative_Fuel_Economy_Highway',
 'Alternative_Fuel_Economy_Combined',
 'Conventional_Fuel_Economy_City',
 'Conventional_Fuel_Economy_Highway',
 'Conventional_Fuel_Economy_Combined',
 'Transmission_Type',
 'Engine_Type',
 'Engine_Size',
 'Engine_Cylinder_Count',
 'Engine_Description',
 'Manufacturer',
 'Category',
 'Fuel',
 'Electric-Only_Range',
 'PHEV_Total_Range',
 'PHEV_Type',
 'Drivetrain',
 'combined_fuel_economy',
 'Name']
In [16]:
fuels=df['Fuel'].unique().tolist()
fuels
Out[16]:
['Hybrid Electric',
 'Plug-in Hybrid Electric',
 'Electric',
 'Biodiesel (B20)',
 'Ethanol (E85)',
 'Hydrogen Fuel Cell',
 'Propane - Bi-fuel',
 'CNG - Bi-fuel',
 'CNG - Compressed Natural Gas',
 'Propane',
 'Methanol',
 nan]
In [17]:
man=df['Manufacturer'].unique().tolist()
man
Out[17]:
['Acura',
 'Audi',
 'Bentley Motors',
 'BMW',
 'Cadillac',
 'Chevrolet',
 'Chrysler',
 'Ferrari',
 'Ford',
 'GMC',
 'Honda',
 'Hyundai',
 'Jaguar',
 'Jeep',
 'Kia',
 'Land Rover',
 'Lexus',
 'Lincoln',
 'Lucid USA, Inc.',
 'Mazda',
 'Mercedes-Benz',
 'Mini',
 'Mitsubishi',
 'Nissan',
 'Polestar Automotive USA',
 'Porsche',
 'Ram',
 'Rivian ',
 'Tesla',
 'Toyota',
 'Volkswagen',
 'Volvo',
 'Kandi',
 'Karma',
 'Subaru',
 'Buick',
 'BYD Motors',
 'Dodge',
 'Fiat',
 'smart',
 'Infiniti',
 'McLaren',
 'Scion',
 'Coda Automotive',
 'Vehicle Production Group',
 'Fisker Automotive',
 'Wheego Electric Cars, Inc.',
 'Mercury',
 'Saab',
 'HUMMER',
 'Saturn',
 'Solectria',
 'QUANTUM-PROCON',
 'General Motors EV',
 'Plymouth',
 nan]
In [18]:
df2 = df[["Manufacturer","Fuel","Category","Drivetrain"]]
df2
Out[18]:
Manufacturer Fuel Category Drivetrain
0 Acura Hybrid Electric Sedan/Wagon AWD
1 Audi Hybrid Electric Sedan/Wagon FWD
2 Audi Hybrid Electric Sedan/Wagon AWD
3 Audi Hybrid Electric Sedan/Wagon AWD
4 Audi Hybrid Electric Sedan/Wagon AWD
... ... ... ... ...
1048570 NaN NaN NaN NaN
1048571 NaN NaN NaN NaN
1048572 NaN NaN NaN NaN
1048573 NaN NaN NaN NaN
1048574 NaN NaN NaN NaN

1048575 rows × 4 columns

In [19]:
df_hybrid=df.query("Fuel == 'Hybrid Electric'")
df_hybrid
Out[19]:
Unnamed: 0 Vehicle_ID Model Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined ... Engine_Description Manufacturer Category Fuel Electric-Only_Range PHEV_Total_Range PHEV_Type Drivetrain combined_fuel_economy Name
0 0 13044.0 NSX 2022.0 NaN NaN NaN 21.0 22.0 21.0 ... 3.5L V6 Acura Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Acura NSX
1 1 12854.0 A3 2022.0 NaN NaN NaN 29.0 38.0 32.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN FWD NaN Audi A3
2 2 12842.0 A3 quattro 2022.0 NaN NaN NaN 28.0 36.0 31.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A3 quattro
3 3 12783.0 A4 allroad quattro 2022.0 NaN NaN NaN 24.0 30.0 26.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 allroad quattro
4 4 12782.0 A4 quattro 2022.0 NaN NaN NaN 26.0 34.0 29.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 quattro
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2794 2794 545.0 Prius Hybrid 2002.0 60.0 51.0 NaN NaN NaN NaN ... NaN Toyota Sedan/Wagon Hybrid Electric NaN NaN NaN NaN NaN Toyota Prius Hybrid
2829 2829 139.0 Insight 2001.0 61.0 70.0 NaN NaN NaN NaN ... NaN Honda Sedan/Wagon Hybrid Electric NaN NaN NaN NaN NaN Honda Insight
2838 2838 140.0 Prius Hybrid 2001.0 52.0 45.0 NaN NaN NaN NaN ... NaN Toyota Sedan/Wagon Hybrid Electric NaN NaN NaN NaN NaN Toyota Prius Hybrid
2870 2870 80.0 Insight 2000.0 61.0 70.0 NaN 20.0 26.0 NaN ... NaN Honda Sedan/Wagon Hybrid Electric NaN NaN NaN NaN NaN Honda Insight
2877 2877 574.0 Prius 2000.0 52.0 45.0 NaN NaN NaN NaN ... NaN Toyota Sedan/Wagon Hybrid Electric NaN NaN NaN NaN NaN Toyota Prius

810 rows × 24 columns

In [20]:
df_phev=df.query("Fuel == 'Plug-in Hybrid Electric'")
df_phev
Out[20]:
Unnamed: 0 Vehicle_ID Model Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined ... Engine_Description Manufacturer Category Fuel Electric-Only_Range PHEV_Total_Range PHEV_Type Drivetrain combined_fuel_economy Name
14 14 12831.0 A7 TFSI e quattro 2022.0 NaN NaN 70.0 25.0 31.0 27.0 ... 2.0L I4;\r\n105 kW electric motor Audi Sedan/Wagon Plug-in Hybrid Electric 26.0 410.0 Blended AWD 97.0 Audi A7 TFSI e quattro
28 28 12832.0 Q5 TFSI e quattro 2022.0 NaN NaN 61.0 25.0 27.0 26.0 ... 2.0L I4;\r\n105 kW electric motor Audi Sedan/Wagon Plug-in Hybrid Electric 23.0 390.0 Blended AWD 87.0 Audi Q5 TFSI e quattro
38 38 13090.0 Flying Spur Hybrid 2022.0 NaN NaN 46.0 17.0 22.0 19.0 ... 2.9L V6;\r\n103 kW electric motor Bentley Motors Sedan/Wagon Plug-in Hybrid Electric 21.0 430.0 Blended NaN 65.0 Bentley Motors Flying Spur Hybrid
39 39 13067.0 330e Sedan 2022.0 NaN NaN 75.0 25.0 33.0 28.0 ... 2.0L I4;\r\n80 kW electric motor BMW Sedan/Wagon Plug-in Hybrid Electric 23.0 320.0 Blended RWD 103.0 BMW 330e Sedan
40 40 13068.0 330e xDrive 2022.0 NaN NaN 67.0 22.0 30.0 25.0 ... 2.0L I4;\r\n80 kW electric motor BMW Sedan/Wagon Plug-in Hybrid Electric 20.0 290.0 Blended AWD 92.0 BMW 330e xDrive
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2127 2127 10553.0 Prius Plug-in 2013.0 NaN NaN NaN 51.0 49.0 NaN ... NaN Toyota Sedan/Wagon Plug-in Hybrid Electric NaN NaN NaN NaN NaN Toyota Prius Plug-in
2167 2167 10351.0 Volt 2012.0 95.00 93.00 NaN 35.0 40.0 NaN ... NaN Chevrolet Sedan/Wagon Plug-in Hybrid Electric NaN NaN NaN NaN NaN Chevrolet Volt
2179 2179 10660.0 Karma 2012.0 62.00 63.00 NaN 20.0 21.0 NaN ... NaN Fisker Automotive Sedan/Wagon Plug-in Hybrid Electric NaN NaN NaN NaN NaN Fisker Automotive Karma
2250 2250 10385.0 Prius Plug-in Hybrid 2012.0 95.00 NaN NaN 51.0 49.0 NaN ... NaN Toyota Sedan/Wagon Plug-in Hybrid Electric NaN NaN NaN NaN NaN Toyota Prius Plug-in Hybrid
2290 2290 10126.0 Volt 2011.0 0.36 0.37 NaN 35.0 40.0 NaN ... NaN Chevrolet Sedan/Wagon Plug-in Hybrid Electric NaN NaN NaN NaN NaN Chevrolet Volt

282 rows × 24 columns

In [21]:
display(df['Manufacturer'].value_counts())
Ford                          533
Chevrolet                     371
GMC                           252
Toyota                        193
Mercedes-Benz                 146
Dodge                         130
Audi                          118
BMW                           110
Honda                          99
Tesla                          93
Lexus                          87
Porsche                        76
Nissan                         76
Hyundai                        74
Chrysler                       63
Ram                            57
Kia                            53
Land Rover                     49
Jeep                           38
Jaguar                         34
Volvo                          33
Cadillac                       33
Lincoln                        28
Volkswagen                     25
Buick                          23
Mercury                        23
Infiniti                       22
Bentley Motors                 20
Acura                          18
Mazda                          14
Mitsubishi                     12
Solectria                      12
smart                          10
General Motors EV               9
Mini                            8
Fiat                            7
Subaru                          6
Lucid USA, Inc.                 6
Karma                           5
Polestar Automotive USA         5
Saturn                          5
QUANTUM-PROCON                  4
Plymouth                        4
Ferrari                         4
Vehicle Production Group        3
McLaren                         3
BYD Motors                      3
Coda Automotive                 2
Scion                           2
Rivian                          2
Fisker Automotive               1
Wheego Electric Cars, Inc.      1
Saab                            1
HUMMER                          1
Kandi                           1
Name: Manufacturer, dtype: int64
In [23]:
#because ford has the most counts from above
df2=df[['Manufacturer','Fuel']]
df2['count']=1
df3=df2.groupby(['Manufacturer','Fuel'],as_index=False).sum()
display(df3)
C:\Users\sarahd\AppData\Local\Temp\ipykernel_19892\1095849869.py:3: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  df2['count']=1
Manufacturer Fuel count
0 Acura Hybrid Electric 18
1 Audi Electric 14
2 Audi Ethanol (E85) 23
3 Audi Hybrid Electric 70
4 Audi Plug-in Hybrid Electric 11
... ... ... ...
143 Volkswagen Hybrid Electric 9
144 Volvo Electric 3
145 Volvo Plug-in Hybrid Electric 30
146 Wheego Electric Cars, Inc. Electric 1
147 smart Electric 10

148 rows × 3 columns

In [24]:
for man in man:
    df_ford=df3[df3['Manufacturer']==man].sort_values('count',ascending=False)
    display(df_ford)
Manufacturer Fuel count
0 Acura Hybrid Electric 18
Manufacturer Fuel count
3 Audi Hybrid Electric 70
2 Audi Ethanol (E85) 23
1 Audi Electric 14
4 Audi Plug-in Hybrid Electric 11
Manufacturer Fuel count
9 Bentley Motors Ethanol (E85) 17
10 Bentley Motors Plug-in Hybrid Electric 3
Manufacturer Fuel count
7 BMW Plug-in Hybrid Electric 49
6 BMW Hybrid Electric 41
5 BMW Electric 20
Manufacturer Fuel count
14 Cadillac Ethanol (E85) 19
15 Cadillac Hybrid Electric 5
16 Cadillac Plug-in Hybrid Electric 5
13 Cadillac Biodiesel (B20) 4
Manufacturer Fuel count
21 Chevrolet Ethanol (E85) 191
17 Chevrolet Biodiesel (B20) 64
19 Chevrolet CNG - Compressed Natural Gas 45
22 Chevrolet Hybrid Electric 25
20 Chevrolet Electric 17
25 Chevrolet Propane 11
24 Chevrolet Plug-in Hybrid Electric 9
18 Chevrolet CNG - Bi-fuel 6
23 Chevrolet Methanol 2
26 Chevrolet Propane - Bi-fuel 1
Manufacturer Fuel count
28 Chrysler Ethanol (E85) 54
30 Chrysler Plug-in Hybrid Electric 6
27 Chrysler Electric 2
29 Chrysler Hybrid Electric 1
Manufacturer Fuel count
38 Ferrari Plug-in Hybrid Electric 3
37 Ferrari Hybrid Electric 1
Manufacturer Fuel count
45 Ford Ethanol (E85) 188
43 Ford CNG - Compressed Natural Gas 94
46 Ford Hybrid Electric 52
49 Ford Propane 48
44 Ford Electric 43
41 Ford Biodiesel (B20) 32
42 Ford CNG - Bi-fuel 23
48 Ford Plug-in Hybrid Electric 23
50 Ford Propane - Bi-fuel 22
47 Ford Methanol 8
Manufacturer Fuel count
54 GMC Ethanol (E85) 128
51 GMC Biodiesel (B20) 53
53 GMC CNG - Compressed Natural Gas 36
55 GMC Hybrid Electric 18
56 GMC Propane 12
52 GMC CNG - Bi-fuel 4
57 GMC Propane - Bi-fuel 1
Manufacturer Fuel count
62 Honda Hybrid Electric 57
60 Honda CNG - Compressed Natural Gas 17
63 Honda Hydrogen Fuel Cell 11
61 Honda Electric 8
64 Honda Plug-in Hybrid Electric 6
Manufacturer Fuel count
66 Hyundai Hybrid Electric 40
65 Hyundai Electric 12
67 Hyundai Hydrogen Fuel Cell 11
68 Hyundai Plug-in Hybrid Electric 11
Manufacturer Fuel count
70 Jaguar Biodiesel (B20) 14
72 Jaguar Ethanol (E85) 11
73 Jaguar Hybrid Electric 6
71 Jaguar Electric 3
Manufacturer Fuel count
75 Jeep Ethanol (E85) 21
76 Jeep Hybrid Electric 11
74 Jeep Biodiesel (B20) 3
77 Jeep Plug-in Hybrid Electric 3
Manufacturer Fuel count
81 Kia Hybrid Electric 31
80 Kia Electric 12
82 Kia Plug-in Hybrid Electric 10
Manufacturer Fuel count
85 Land Rover Hybrid Electric 26
83 Land Rover Biodiesel (B20) 11
84 Land Rover Ethanol (E85) 7
86 Land Rover Plug-in Hybrid Electric 5
Manufacturer Fuel count
87 Lexus Hybrid Electric 86
88 Lexus Plug-in Hybrid Electric 1
Manufacturer Fuel count
89 Lincoln Ethanol (E85) 13
90 Lincoln Hybrid Electric 10
91 Lincoln Plug-in Hybrid Electric 5
Manufacturer Fuel count
92 Lucid USA, Inc. Electric 6
Manufacturer Fuel count
94 Mazda Ethanol (E85) 9
95 Mazda Hybrid Electric 4
93 Mazda Electric 1
Manufacturer Fuel count
99 Mercedes-Benz Hybrid Electric 82
98 Mercedes-Benz Ethanol (E85) 42
101 Mercedes-Benz Plug-in Hybrid Electric 13
97 Mercedes-Benz Electric 6
100 Mercedes-Benz Hydrogen Fuel Cell 3
Manufacturer Fuel count
105 Mini Plug-in Hybrid Electric 5
104 Mini Electric 3
Manufacturer Fuel count
106 Mitsubishi Electric 6
108 Mitsubishi Plug-in Hybrid Electric 5
107 Mitsubishi Ethanol (E85) 1
Manufacturer Fuel count
110 Nissan Electric 29
111 Nissan Ethanol (E85) 28
112 Nissan Hybrid Electric 16
109 Nissan Biodiesel (B20) 3
Manufacturer Fuel count
115 Polestar Automotive USA Electric 3
116 Polestar Automotive USA Plug-in Hybrid Electric 2
Manufacturer Fuel count
119 Porsche Plug-in Hybrid Electric 52
117 Porsche Electric 19
118 Porsche Hybrid Electric 5
Manufacturer Fuel count
125 Ram Hybrid Electric 20
121 Ram Biodiesel (B20) 16
124 Ram Ethanol (E85) 15
122 Ram CNG - Bi-fuel 3
123 Ram CNG - Compressed Natural Gas 3
Manufacturer Fuel count
126 Rivian Electric 2
Manufacturer Fuel count
133 Tesla Electric 93
Manufacturer Fuel count
137 Toyota Hybrid Electric 133
136 Toyota Ethanol (E85) 21
139 Toyota Plug-in Hybrid Electric 13
135 Toyota Electric 12
138 Toyota Hydrogen Fuel Cell 11
134 Toyota CNG - Compressed Natural Gas 3
Manufacturer Fuel count
141 Volkswagen Electric 14
143 Volkswagen Hybrid Electric 9
142 Volkswagen Ethanol (E85) 2
Manufacturer Fuel count
145 Volvo Plug-in Hybrid Electric 30
144 Volvo Electric 3
Manufacturer Fuel count
78 Kandi Electric 1
Manufacturer Fuel count
79 Karma Plug-in Hybrid Electric 5
Manufacturer Fuel count
131 Subaru Hybrid Electric 3
132 Subaru Plug-in Hybrid Electric 3
Manufacturer Fuel count
11 Buick Ethanol (E85) 19
12 Buick Hybrid Electric 4
Manufacturer Fuel count
8 BYD Motors Electric 3
Manufacturer Fuel count
34 Dodge Ethanol (E85) 94
32 Dodge CNG - Compressed Natural Gas 28
36 Dodge Methanol 4
33 Dodge Electric 3
35 Dodge Hybrid Electric 1
Manufacturer Fuel count
39 Fiat Electric 7
Manufacturer Fuel count
147 smart Electric 10
Manufacturer Fuel count
69 Infiniti Hybrid Electric 22
Manufacturer Fuel count
96 McLaren Plug-in Hybrid Electric 3
Manufacturer Fuel count
129 Scion Electric 2
Manufacturer Fuel count
31 Coda Automotive Electric 2
Manufacturer Fuel count
140 Vehicle Production Group CNG - Compressed Natural Gas 3
Manufacturer Fuel count
40 Fisker Automotive Plug-in Hybrid Electric 1
Manufacturer Fuel count
146 Wheego Electric Cars, Inc. Electric 1
Manufacturer Fuel count
102 Mercury Ethanol (E85) 15
103 Mercury Hybrid Electric 8
Manufacturer Fuel count
127 Saab Ethanol (E85) 1
Manufacturer Fuel count
59 HUMMER Ethanol (E85) 1
Manufacturer Fuel count
128 Saturn Hybrid Electric 5
Manufacturer Fuel count
130 Solectria Electric 12
Manufacturer Fuel count
120 QUANTUM-PROCON Propane 4
Manufacturer Fuel count
58 General Motors EV Electric 9
Manufacturer Fuel count
113 Plymouth CNG - Compressed Natural Gas 2
114 Plymouth Ethanol (E85) 2
Manufacturer Fuel count
In [25]:
df['combined_fuel_economy'] = df["Alternative_Fuel_Economy_Combined"] + df["Conventional_Fuel_Economy_Combined"]
print('Updated DataFrame:')
print(df)
Updated DataFrame:
         Unnamed: 0  Vehicle_ID               Model  Model_Year  \
0                 0     13044.0                 NSX      2022.0   
1                 1     12854.0                 A3       2022.0   
2                 2     12842.0          A3 quattro      2022.0   
3                 3     12783.0  A4 allroad quattro      2022.0   
4                 4     12782.0          A4 quattro      2022.0   
...             ...         ...                 ...         ...   
1048570     1048570         NaN                 NaN         NaN   
1048571     1048571         NaN                 NaN         NaN   
1048572     1048572         NaN                 NaN         NaN   
1048573     1048573         NaN                 NaN         NaN   
1048574     1048574         NaN                 NaN         NaN   

         Alternative_Fuel_Economy_City  Alternative_Fuel_Economy_Highway  \
0                                  NaN                               NaN   
1                                  NaN                               NaN   
2                                  NaN                               NaN   
3                                  NaN                               NaN   
4                                  NaN                               NaN   
...                                ...                               ...   
1048570                            NaN                               NaN   
1048571                            NaN                               NaN   
1048572                            NaN                               NaN   
1048573                            NaN                               NaN   
1048574                            NaN                               NaN   

         Alternative_Fuel_Economy_Combined  Conventional_Fuel_Economy_City  \
0                                      NaN                            21.0   
1                                      NaN                            29.0   
2                                      NaN                            28.0   
3                                      NaN                            24.0   
4                                      NaN                            26.0   
...                                    ...                             ...   
1048570                                NaN                             NaN   
1048571                                NaN                             NaN   
1048572                                NaN                             NaN   
1048573                                NaN                             NaN   
1048574                                NaN                             NaN   

         Conventional_Fuel_Economy_Highway  \
0                                     22.0   
1                                     38.0   
2                                     36.0   
3                                     30.0   
4                                     34.0   
...                                    ...   
1048570                                NaN   
1048571                                NaN   
1048572                                NaN   
1048573                                NaN   
1048574                               66.0   

         Conventional_Fuel_Economy_Combined  ... Engine_Description  \
0                                      21.0  ...            3.5L V6   
1                                      32.0  ...            2.0L I4   
2                                      31.0  ...            2.0L I4   
3                                      26.0  ...            2.0L I4   
4                                      29.0  ...            2.0L I4   
...                                     ...  ...                ...   
1048570                                 NaN  ...                NaN   
1048571                                 NaN  ...                NaN   
1048572                                 NaN  ...                NaN   
1048573                                 NaN  ...                NaN   
1048574                                 NaN  ...                NaN   

        Manufacturer     Category             Fuel Electric-Only_Range  \
0              Acura  Sedan/Wagon  Hybrid Electric                 NaN   
1               Audi  Sedan/Wagon  Hybrid Electric                 NaN   
2               Audi  Sedan/Wagon  Hybrid Electric                 NaN   
3               Audi  Sedan/Wagon  Hybrid Electric                 NaN   
4               Audi  Sedan/Wagon  Hybrid Electric                 NaN   
...              ...          ...              ...                 ...   
1048570          NaN          NaN              NaN                 NaN   
1048571          NaN          NaN              NaN                 NaN   
1048572          NaN          NaN              NaN                 NaN   
1048573          NaN          NaN              NaN                 NaN   
1048574          NaN          NaN              NaN                 NaN   

        PHEV_Total_Range PHEV_Type Drivetrain  combined_fuel_economy  \
0                    NaN       NaN        AWD                    NaN   
1                    NaN       NaN        FWD                    NaN   
2                    NaN       NaN        AWD                    NaN   
3                    NaN       NaN        AWD                    NaN   
4                    NaN       NaN        AWD                    NaN   
...                  ...       ...        ...                    ...   
1048570              NaN       NaN        NaN                    NaN   
1048571              NaN       NaN        NaN                    NaN   
1048572              NaN       NaN        NaN                    NaN   
1048573              NaN       NaN        NaN                    NaN   
1048574              NaN       NaN        NaN                    NaN   

                            Name  
0                      Acura NSX  
1                       Audi A3   
2                Audi A3 quattro  
3        Audi A4 allroad quattro  
4                Audi A4 quattro  
...                          ...  
1048570                      NaN  
1048571                      NaN  
1048572                      NaN  
1048573                      NaN  
1048574                      NaN  

[1048575 rows x 24 columns]
In [26]:
df['Name']=df['Manufacturer']+' '+df['Model']
df_fe=df[df['Fuel']=='Electric'].reset_index(drop=True)
display(df_fe[0:2].T)
df_fe_aer=df_fe[['Name', 'Electric-Only_Range']]
df_fe_aer=df_fe_aer.dropna().drop_duplicates()
df_fe_aer=df_fe_aer.groupby('Name',as_index=False).max()
df_fe_aer=df_fe_aer.sort_values('Electric-Only_Range',ascending=False)
0 1
Unnamed: 0 16 17
Vehicle_ID 12949.0 13002.0
Model e-tron GT e-tron quattro
Model_Year 2022.0 2022.0
Alternative_Fuel_Economy_City 81.0 78.0
Alternative_Fuel_Economy_Highway 83.0 77.0
Alternative_Fuel_Economy_Combined 82.0 78.0
Conventional_Fuel_Economy_City NaN NaN
Conventional_Fuel_Economy_Highway NaN NaN
Conventional_Fuel_Economy_Combined NaN NaN
Transmission_Type Auto Auto
Engine_Type e-motor e-motor
Engine_Size 175 kW electric motor 141 kW and 172 kW electric motor
Engine_Cylinder_Count NaN NaN
Engine_Description 175 kW electric motor;\r\n129 Ah battery 141 kW and 172 kW electric motors;\r\n240 Ah b...
Manufacturer Audi Audi
Category Sedan/Wagon SUV
Fuel Electric Electric
Electric-Only_Range 238.0 222.0
PHEV_Total_Range NaN NaN
PHEV_Type NaN NaN
Drivetrain AWD AWD
combined_fuel_economy NaN NaN
Name Audi e-tron GT Audi e-tron quattro
In [27]:
import os
import random
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import csv
In [28]:
fig = px.bar(df_fe_aer[:30], x='Name', y='Electric-Only_Range', title="Electric Car Ranking", height=700)
fig.show()
In [29]:
df_ph=df[df['Fuel']=='Plug-in Hybrid Electric'].reset_index(drop=True)
display(df_ph[0:2].T)
df_ph_aer=df_ph[['Name', 'PHEV_Total_Range']]
df_ph_aer=df_ph_aer.dropna().drop_duplicates()
df_ph_aer=df_ph_aer.groupby('Name',as_index=False).max()
df_ph_aer=df_ph_aer.sort_values('PHEV_Total_Range',ascending=False)
fig2 = px.bar(df_ph_aer[:30], x='Name', y='PHEV_Total_Range', title="Plug In Hybrid Range", height=700)
fig2.show()
0 1
Unnamed: 0 14 28
Vehicle_ID 12831.0 12832.0
Model A7 TFSI e quattro Q5 TFSI e quattro
Model_Year 2022.0 2022.0
Alternative_Fuel_Economy_City NaN NaN
Alternative_Fuel_Economy_Highway NaN NaN
Alternative_Fuel_Economy_Combined 70.0 61.0
Conventional_Fuel_Economy_City 25.0 25.0
Conventional_Fuel_Economy_Highway 31.0 27.0
Conventional_Fuel_Economy_Combined 27.0 26.0
Transmission_Type Auto Auto
Engine_Type SI SI
Engine_Size 2.0L 2.0L
Engine_Cylinder_Count 4.0 4.0
Engine_Description 2.0L I4;\r\n105 kW electric motor 2.0L I4;\r\n105 kW electric motor
Manufacturer Audi Audi
Category Sedan/Wagon Sedan/Wagon
Fuel Plug-in Hybrid Electric Plug-in Hybrid Electric
Electric-Only_Range 26.0 23.0
PHEV_Total_Range 410.0 390.0
PHEV_Type Blended Blended
Drivetrain AWD AWD
combined_fuel_economy 97.0 87.0
Name Audi A7 TFSI e quattro Audi Q5 TFSI e quattro
In [37]:
df[(df['Engine_Cylinder_Count'] == 6) & (df['Category'] == "SUV")]
Out[37]:
Unnamed: 0 Vehicle_ID Model Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined ... Engine_Description Manufacturer Category Fuel Electric-Only_Range PHEV_Total_Range PHEV_Type Drivetrain combined_fuel_economy Name
29 29 12797.0 Q7 quattro 2022.0 NaN NaN NaN 18.0 23.0 20.0 ... 3.0L V6 Audi SUV Hybrid Electric NaN NaN NaN AWD NaN Audi Q7 quattro
30 30 12798.0 Q8 quattro 2022.0 NaN NaN NaN 18.0 23.0 20.0 ... 3.0L V6 Audi SUV Hybrid Electric NaN NaN NaN AWD NaN Audi Q8 quattro
60 60 12860.0 X3 M40i 2022.0 NaN NaN NaN 21.0 26.0 23.0 ... 3.0L V6 BMW SUV Hybrid Electric NaN NaN NaN AWD NaN BMW X3 M40i
61 61 12861.0 X4 M40i 2022.0 NaN NaN NaN 21.0 26.0 23.0 ... 3.0L V6 BMW SUV Hybrid Electric NaN NaN NaN AWD NaN BMW X4 M40i
62 62 12862.0 X5 sDrive40i 2022.0 NaN NaN NaN 21.0 26.0 23.0 ... 3.0L V6 BMW SUV Hybrid Electric NaN NaN NaN RWD NaN BMW X5 sDrive40i
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2741 2741 556.0 Mountaineer Wagon FFV 2003.0 NaN NaN NaN NaN NaN NaN ... NaN Mercury SUV Ethanol (E85) NaN NaN NaN NaN NaN Mercury Mountaineer Wagon FFV
2766 2766 179.0 Explorer FFV 2002.0 11.0 15.0 NaN 15.0 20.0 NaN ... NaN Ford SUV Ethanol (E85) NaN NaN NaN NaN NaN Ford Explorer FFV
2767 2767 180.0 Explorer Sport FFV 2002.0 18.0 22.0 NaN NaN NaN NaN ... NaN Ford SUV Ethanol (E85) NaN NaN NaN NaN NaN Ford Explorer Sport FFV
2768 2768 181.0 Explorer Sport Track FFV 2002.0 16.0 20.0 NaN NaN NaN NaN ... NaN Ford SUV Ethanol (E85) NaN NaN NaN NaN NaN Ford Explorer Sport Track FFV
2813 2813 138.0 Explorer Sport FFV 2001.0 12.0 16.0 NaN 16.0 21.0 NaN ... NaN Ford SUV Ethanol (E85) NaN NaN NaN NaN NaN Ford Explorer Sport FFV

259 rows × 24 columns

In [39]:
df[(df['Engine_Cylinder_Count'] == 4) & (df['Category'] == "SUV")]
Out[39]:
Unnamed: 0 Vehicle_ID Model Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined ... Engine_Description Manufacturer Category Fuel Electric-Only_Range PHEV_Total_Range PHEV_Type Drivetrain combined_fuel_economy Name
25 25 13023.0 Q5 quattro 2022.0 NaN NaN NaN 23.0 28.0 25.0 ... 2.0L I4 Audi SUV Hybrid Electric NaN NaN NaN AWD NaN Audi Q5 quattro
26 26 12795.0 Q5 S line quattro 2022.0 NaN NaN NaN 23.0 28.0 25.0 ... 2.0L I4 Audi SUV Hybrid Electric NaN NaN NaN AWD NaN Audi Q5 S line quattro
27 27 12796.0 Q5 Sportback S line quattro 2022.0 NaN NaN NaN 23.0 28.0 25.0 ... 2.0L I4 Audi SUV Hybrid Electric NaN NaN NaN AWD NaN Audi Q5 Sportback S line quattro
90 90 13037.0 Escape FWD PHEV 2022.0 NaN NaN 105.0 43.0 38.0 41.0 ... 2.5L I4;\r\n96 kW electric motor Ford SUV Plug-in Hybrid Electric 37.0 520.0 Blended FWD 146.0 Ford Escape FWD PHEV
134 134 12886.0 CR-V AWD 2022.0 NaN NaN NaN 40.0 35.0 38.0 ... 2.0L I4 Honda SUV Hybrid Electric NaN NaN NaN AWD NaN Honda CR-V AWD
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2578 2578 680.0 Mariner 2007.0 33.0 29.0 NaN NaN NaN NaN ... NaN Mercury SUV Hybrid Electric NaN NaN NaN NaN NaN Mercury Mariner
2582 2582 697.0 VUE Green Line 2007.0 27.0 32.0 NaN NaN NaN NaN ... NaN Saturn SUV Hybrid Electric NaN NaN NaN NaN NaN Saturn VUE Green Line
2604 2604 489.0 Escape Hybrid 2006.0 36.0 31.0 NaN NaN NaN NaN ... NaN Ford SUV Hybrid Electric NaN NaN NaN NaN NaN Ford Escape Hybrid
2619 2619 492.0 Mariner Hybrid 2006.0 33.0 29.0 NaN NaN NaN NaN ... NaN Mercury SUV Hybrid Electric NaN NaN NaN NaN NaN Mercury Mariner Hybrid
2636 2636 375.0 Escape Hybrid 2005.0 36.0 31.0 NaN NaN NaN NaN ... NaN Ford SUV Hybrid Electric NaN NaN NaN NaN NaN Ford Escape Hybrid

180 rows × 24 columns

In [41]:
new_df = pd.DataFrame(df)
df.update(new_df)
In [42]:
new_df
Out[42]:
Unnamed: 0 Vehicle_ID Model Model_Year Alternative_Fuel_Economy_City Alternative_Fuel_Economy_Highway Alternative_Fuel_Economy_Combined Conventional_Fuel_Economy_City Conventional_Fuel_Economy_Highway Conventional_Fuel_Economy_Combined ... Engine_Description Manufacturer Category Fuel Electric-Only_Range PHEV_Total_Range PHEV_Type Drivetrain combined_fuel_economy Name
0 0 13044.0 NSX 2022.0 NaN NaN NaN 21.0 22.0 21.0 ... 3.5L V6 Acura Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Acura NSX
1 1 12854.0 A3 2022.0 NaN NaN NaN 29.0 38.0 32.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN FWD NaN Audi A3
2 2 12842.0 A3 quattro 2022.0 NaN NaN NaN 28.0 36.0 31.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A3 quattro
3 3 12783.0 A4 allroad quattro 2022.0 NaN NaN NaN 24.0 30.0 26.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 allroad quattro
4 4 12782.0 A4 quattro 2022.0 NaN NaN NaN 26.0 34.0 29.0 ... 2.0L I4 Audi Sedan/Wagon Hybrid Electric NaN NaN NaN AWD NaN Audi A4 quattro
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
1048570 1048570 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048571 1048571 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048572 1048572 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048573 1048573 NaN NaN NaN NaN NaN NaN NaN NaN NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1048574 1048574 NaN NaN NaN NaN NaN NaN NaN 66.0 NaN ... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

1048575 rows × 24 columns

In [43]:
df.to_csv('Lightdutyvehicles_final.txt')
In [ ]: